/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package giormi;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.swing.JOptionPane;
import javax.swing.JTable;
/**
 *
 * @author giorgio
 */
public class searchproduct {
    private String p_name;
    private boolean state;
    private JTable ptable;
    public searchproduct(String p_name, boolean state, JTable ptable){
        this.p_name = p_name;
        this.state = state;
        this.ptable = ptable;
    }
    
    private Connection conn;
    private String SQLStatement;
    private String URL;
    private Statement stmt;
    public ResultSet rset;    
    
     public int gettablerows(){
        int total=0;
        connect();
        try {
            
               
                SQLStatement = "SELECT COUNT(p_name) from product";

    
           
            rset = stmt.executeQuery(SQLStatement);
            while (rset.next()) {
                total = rset.getInt(1); // store result to total
            }
            rset.close();
            disconnect();
        } catch(Exception e){
            System.out.println(e.getMessage());
            JOptionPane.showMessageDialog(null,e.getMessage(),"ERROR", JOptionPane.WARNING_MESSAGE);
        }
        return total;
    }

    public void erase(){
        for (int i=0; i<gettablerows(); i++){ // row count
            ptable.setValueAt("", i, 0);
            ptable.setValueAt("", i, 1);
            ptable.setValueAt("", i, 2);
            ptable.setValueAt("", i, 3);
            ptable.setValueAt("", i, 4);
            ptable.setValueAt("", i, 5);
        }
    }
    
public void connect(){
        try{
            //1
//            URL = "jdbc:odbc:introdb";
//            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//            conn = DriverManager.getConnection(URL,"root","huhu");
            DBConnection db = new DBConnection();
            conn = DBConnection.get();
            //2
            stmt = conn.createStatement();
        }
        catch(Exception e){
            System.out.println(e.getMessage());
            JOptionPane.showMessageDialog(null,e.getMessage(),"ERROR", JOptionPane.WARNING_MESSAGE);
        }
    }

    public void disconnect(){
        try{
            //5
            stmt.close();
            conn.close();
        }
        catch(Exception ex){
            System.out.println(ex.getMessage());
            JOptionPane.showMessageDialog(null,ex.getMessage(),"ERROR", JOptionPane.WARNING_MESSAGE);
        }
    }

    public void searchproductbyname(){
        erase();
        connect();
        int i=0;
        
        if(state){
            
        try{
           SQLStatement = "SELECT * FROM product WHERE p_status = 'Active' AND p_name LIKE '%" +p_name+"%'";
             rset = stmt.executeQuery(SQLStatement);
            while (rset.next()) {
                ptable.setValueAt(rset.getString("idproduct"), i, 0);
                ptable.setValueAt(rset.getString("p_name"), i, 1);
                ptable.setValueAt(rset.getString("p_netweight"), i, 2);
                ptable.setValueAt(rset.getString("p_barcode"), i, 3);
                ptable.setValueAt(rset.getString("p_status"), i, 4);
                ptable.setValueAt(rset.getString("p_packing"), i, 5);
                i++;
            }
            rset.close();
            disconnect();
        }
        catch(Exception ex){
            System.out.println(ex.getMessage());
            JOptionPane.showMessageDialog(null,ex.getMessage(),"ERROR", JOptionPane.WARNING_MESSAGE);
        }
                        }
                
        else{
            try{
           SQLStatement = "SELECT * FROM product WHERE p_name LIKE '%" +p_name+"%'";
             rset = stmt.executeQuery(SQLStatement);
            while (rset.next()) {
                ptable.setValueAt(rset.getString("idproduct"), i, 0);
                ptable.setValueAt(rset.getString("p_name"), i, 1);
                ptable.setValueAt(rset.getString("p_netweight"), i, 2);
                ptable.setValueAt(rset.getString("p_barcode"), i, 3);
                ptable.setValueAt(rset.getString("p_status"), i, 4);
                ptable.setValueAt(rset.getString("p_packing"), i, 5);
                i++;
            }
            rset.close();
            disconnect();
        }
        catch(Exception ex){
            System.out.println(ex.getMessage());
            JOptionPane.showMessageDialog(null,ex.getMessage(),"ERROR", JOptionPane.WARNING_MESSAGE);
            }
            
            
        }
    }
}
